/**
 * Created by think on 2015/1/11.
 */

var execQuery = require("./db"),
    EventEmitter = require('events').EventEmitter,
    util = require('util'),
    Q = require("q"),
  Paginate = require("./paginate").Paginate;

function BaseProxy(table) {
    this.table = table;
}

util.inherits(BaseProxy, EventEmitter);

/**
 * 直接执行sql语句
 *
 * @param sql sql语句
 * @param params sql中占位符
 * @param callback
 * @returns {*}
 */
BaseProxy.prototype.exec = function(sql, params, callback) {
  return execQuery.execQuery(sql, params);
}

/**
 * 根据ID获取单条记录
 * @param id {bigint}
 * @param opts {object}
 *   -- columns 查询列
 * @param callback
 *   -- option ,如果不传递callback将返回promise
 * @returns {*|exports}
 */
BaseProxy.prototype.get = function (id, opts, callback) {
  var defer = Q.defer();
    if (!id) {
      return false;
    }
    if (!opts) {
        opts = {};
    }
    var cols = _get_columns(opts.columns);
    var sql = 'SELECT :columns FROM :table WHERE id=$1::uuid LIMIT 1';
    sql = _wrapperSql(sql, {columns: cols, table: this.table});
    var params = [id];
    return execQuery(sql, params)
      .then(function(result) {
        if (result && result.rows[0]) {
          return result.rows[0];
        } else {
          return false;
        }
      })
      .nodeify(callback);
}

/**
 * 通过查询条件获取单条记录
 * @param query 查询条件，可以是 {key:value}形式，也可以是 "A='x' And B='z'"形式
 * @param opts 可选参数 columns 查询列
 * @param callback 可选参数，如果不传返回promise形式
 * @returns {*|exports}
 */
BaseProxy.prototype.fetchOne = function(query, opts, callback) {
    if (!opts) {
        opts = {};
    }
    var _query = _get_query(query);
    var cols = _get_columns(opts.columns);
    var orderBy = opts.orderBy;
    var sql = 'SELECT :columns FROM :table WHERE :query ';
    if (orderBy) {
      orderBy = _get_orderBy(orderBy);
      sql += ' ORDER BY ' + orderBy;
    }
    sql += ' LIMIT 1';
    sql = _wrapperSql(sql, {columns: cols, query: _query, table: this.table});
    var params = [];
    return execQuery(sql, params)
      .then(function(result) {
        if (result && result.rows[0]) {
          return result.rows[0];
        } else {
          return false;
        }
      })
      .nodeify(callback);
}

BaseProxy.prototype.fetchAll = function(query, opts, callback) {
    if (!opts) {
        opts = {};
    }
    var limit = opts.limit;
    var skip = opts.skip;
    var orderBy = opts.orderBy;
    var _query = _get_query(query);
    var cols = _get_columns(opts.columns);
    var distinct = opts.distinct;
    var sql = '';
    if (distinct) {
      sql = 'SELECT distinct :columns FROM :table WHERE :query '
    } else {
      sql = 'SELECT :columns FROM :table WHERE :query ';
    }
    if (orderBy) {
      orderBy = _get_orderBy(orderBy);
      sql += ' ORDER BY '+ orderBy;
    }
    if (limit) {
      sql += ' LIMIT ' + limit;
    }
    if (skip) {
      sql += ' OFFSET ' + skip;
    }
    sql = _wrapperSql(sql, {columns: cols, query: _query, table: this.table});
    var params = [];
    return execQuery(sql, params)
      .then(function(result) {
        if (result && result.rows) {
          return result.rows;
        } else {
          return false;
        }
      })
      .nodeify(callback);
}

/**
 * 根据上一条ID查询下一页
 *
 * @param lastId
 * @param query
 * @param opts
 * @param callback
 * @returns {*}
 */
BaseProxy.prototype.listByLastId = function(lastId, query, opts, callback) {
  var obj = this;
  if (typeof opts == 'function') {
    callback = opts;
    opts = {};
  }

  if (!opts) {
    opts = {}
  }
  var perPage = opts.perPage || 20;
  opts.limit = perPage;
  query = _get_query(query);
  if (lastId) {
    query = " myuuid(id) > myuuid($1::uuid) AND ";
  }
  //如果没有排序依据，依据uuid先后顺序
  if (!opts.orderBy) {
    opts.orderBy = ["myuuid(id) DESC "];
  }
  return obj.fetchAll(query, opts, callback);
}

BaseProxy.prototype.listAndPaginate = function(query, opts, callback) {
  if (!opts) {
    opts = {};
  }
  var page = opts.page || 1;
  var perPage = opts.perPage || 20;
  var obj = this;
  opts.skip = (page - 1) * perPage;
  opts.limit = perPage;
  return Q.all([
    obj.count(query, opts),
    obj.fetchAll(query, opts)
  ])
    .spread(function(total, items) {
      var pg = new Paginate(page, perPage, total, items);
      return pg;
    })
    .nodeify(callback);
}

BaseProxy.prototype.count = function(query, opts, callback) {
  var query = _get_query(query);
  if (!opts) {
    opts = {};
  }
  var distinct = opts.distinct;
  var columns = opts.columns;
  if (distinct && !columns) {
    columns = ["id"];
  }
  var sql = "";
  if (distinct) {
    sql = "SELECT count(1) as total FROM (SELECT distinct :columns FROM :table WHERE :query) AS R"
  } else {
    sql = "SELECT count(1) as total FROM :table WHERE :query";
  }
  sql = _wrapperSql(sql, {query: query, columns: columns, table: this.table});
  return execQuery(sql, [])
    .then(function(result){
      if (result.rows && result.rows[0]) {
        return result.rows[0]["total"] || 0;
      } else {
        return 0;
      }
    }).nodeify(callback);
}

BaseProxy.prototype.sum = function(sum_column, query, opts, callback) {
  var query = _get_query(query);
  if (!opts) {
    opts = {};
  }
  var sql = "";
  sql = "SELECT sum(:columns) as sum FROM :table WHERE :query";
  sql = _wrapperSql(sql, {query: query, columns: sum_column, table: this.table});
  return execQuery(sql, [])
    .then(function(result){
      if (result.rows && result.rows[0]) {
        return result.rows[0]["sum"] || 0;
      } else {
        return 0;
      }
    }).nodeify(callback);
}

/**
 * 根据ID更新
 * @param id
 * @param updates
 * @param callback
 * @returns {*|exports}
 */
BaseProxy.prototype.update = function(id, updates, returning, callback) {
    var sql = '';
    var params = [];
    if (typeof updates === 'string') {
      updates = _get_updates(updates);
      sql = 'UPDATE :table :update WHERE id=$1::uuid ';
      if (returning) {
        sql += " RETURNING " + returning;
      } else {
        sql += " RETURNING * ";
      }
      sql = _wrapperSql(sql, {update: updates, table: this.table});
      params = [id];
    } else if(typeof updates === 'object') {
      updates = _get_updates(updates);
      var length = updates[1].length;
      sql = 'UPDATE :table :update WHERE id=$'+(length+1)+'::uuid ';
      if (returning) {
        sql += " RETURNING " + returning;
      } else {
        sql += " RETURNING * ";
      }
      sql = _wrapperSql(sql, {update: updates[0], table: this.table});
      params = updates[1];
      params.push(id);
    } else {
      throw new Error('updates only support string or object, you supply is :' + updates);
    }
    return execQuery(sql, params, callback);
}

/**
 * 根据条件更新
 * @param query
 * @param updates
 * @param callback
 * @returns {*|exports}
 */
BaseProxy.prototype.updateAll = function(query, updates, returning, callback) {
    var sql = '';
    var params = [];
    if (typeof updates === 'string') {
      updates = _get_updates(updates);
      query = _get_query(query);
      sql = 'UPDATE :table :update WHERE :query';
      if (returning) {
        sql += " RETURNING " + returning;
      } else {
        sql += " RETURNING * ";
      }
      sql = _wrapperSql(sql, {table: this.table, query: query, update: updates});
    } else {
      updates = _get_updates(updates);
      query = _get_query(query);
      sql = 'UPDATE :table :update WHERE :query';
      if (returning) {
        sql += " RETURNING " + returning;
      } else {
        sql += " RETURNING * ";
      }
      sql = _wrapperSql(sql ,{table: this.table, query: query, update: updates[0]});
      params = updates[1];
    }
    return execQuery(sql, params, callback);
}

BaseProxy.prototype.remove = function(id, returning, callback) {
    var sql = 'DELETE FROM :table WHERE id=$1::uuid';
    if (returning) {
      sql += ' RETURNING ' + returning;
    } else {
      sql += ' RETURNING * ';
    }
    sql = _wrapperSql(sql, {table: this.table});
    var params = [id];
    return execQuery(sql, params, callback);
}

BaseProxy.prototype.removeAll = function(query, returning, callback) {
    query = _get_query(query);
    var sql = 'DELETE FROM :table WHERE :query';
    if (returning) {
      sql += ' RETURNING ' + returning;
    } else {
      sql += ' RETURNING * ';
    }
    sql = _wrapperSql(sql, {table: this.table, query:query});
    var params = [];
    return execQuery(sql, params, callback);
}

/**
 * 添加或保存
 * @param m
 * @param callback
 * @returns {*}
 */
BaseProxy.prototype.save = function(m, returning, callback) {
    var obj = this;
    if (!m) {
        throw new Error('m is '+m);
    } else if (typeof m === 'object') {
        var id = m.id;
        if (id) {
            return obj.update(id, m, returning, callback);
        } else {
            return obj.add(m, returning, callback);
        }
    } else {
        throw new Error('m only be object');
    }
}

/**
 * 添加
 * @param m
 * @param returning 要返回的列
 * @param callback
 * @returns {*|exports}
 */
BaseProxy.prototype.add = function(m, returing, callback) {
    var obj = this;
    var sql = 'INSERT INTO :table ('
    var keys = [];
    var values = [];
    for(var key in m) {
        keys.push(key);
        values.push(m[key]);
    }
    keys = keys.join('","');
    keys = '"' + keys + '"';
    sql += keys +") VALUES(";
    //通过原生指定参数类型方式
    for(var i= 1, ii=values.length; i<=ii; i++) {
      var val = values[i-1];
      if (util.isArray(val)) {
        sql += '$'+i+""
      } else if(typeof val === 'object' && val != null) {
        sql += '$'+i+"::json"
      } else {
        sql += '$'+ i;
      }
      if (i != ii) {
        sql += ',';
      }
    }
//    sql += "'" +values +"')";
     sql +=')';
    if (returing) {
      sql += " RETURNING "+ returing;
    } else {
      //返回所有字段
      sql += " RETURNING * ";
    }
    sql = _wrapperSql(sql, {table: obj.table});
    var params = values;
    return execQuery(sql, params, callback);
}

/**
 * 提取查询列
 * @param columns
 * @returns {string}
 * @private
 */
function _get_columns(columns) {
    var cols = '';
    //如果没有设置，默认查询全部
    if (!columns) {
        cols = '*';
    } else if (util.isArray(columns)) {
        //如果数组长度是0，查询全部
        if (columns.length === 0) {
            cols = '*';
        } else {
            //逗号分隔各个列明
            cols = columns.join(",");
        }
    } else if (typeof columns === 'object') {   //如果是对象类型，转换成 key1 As value1, key2 As Value2形式
        var keys = Object.keys(columns);
        for(var i= 0, ii = keys.length; i< ii; i++) {
            if (i == ii -1) {
                cols += keys[i] + ' AS ' + columns[keys[i]];
            } else {
                cols += keys[i] + ' AS ' + columns[keys[i]] + ',';
            }
        }
    } else if (typeof columns === 'string') {
        cols = columns;
    } else {
        cols = '*';
    }
    //防止SQL注入
    cols = _filter(cols);
    return cols;
}

function _get_orderBy(orderBy) {
  var _order_by = '';
  if (util.isArray(orderBy)) {
    _order_by = orderBy.join(',');
    _order_by = _filter(_order_by);
  } else if (typeof orderBy === 'string') {
    orderBy = _filter(orderBy);
    _order_by = orderBy;
  } else {
    throw new Error("暂时不支持此类型排序参数");
  }
  return _order_by;
}
/**
 * 获取查询条件
 * @param query
 * @returns {string}
 * @private
 */
function _get_query(query) {
    var _query = '';
    if (!query) {
        console.warn("query is null");
    } else if (typeof query === 'string') {
        _query = _filter(query);
    } else if (util.isArray(query)) {
      //如果是数组，当作And处理
      _query = '(';
      for(var i= 0, ii=query.length; i<ii; i++) {
        _query += _get_query(query[i])
        if (i !== ii-1) {
          _query += " AND ";
        }
      }
      _query += ')';
    } else if (typeof query === 'object') {
        var _query = '(';
        var keys = Object.keys(query);
        for(var i= 0, ii= keys.length; i< ii; i++) {
          var key = keys[i];
          var value = query[key];
          //支持or操作
          if (key.toLowerCase() === '$or') {
            if (util.isArray(value)) {
              _query += '('
              //每一项AND，项之间OR
              for(var j= 0, jj= value.length; j <jj; j++) {
                if (j === jj-1) {
                  _query += _get_query(value[j]);
                } else {
                  _query += _get_query(value[j]) + " OR ";
                }
              }
              _query += " ) AND ";
            } else {
              throw new Error('$or can\'t match' + value);
            }
          //支持like 操作
          } else if (key.toLowerCase() === '$like') {
            if (util.isArray(value)) {
              value[1] = _filter(value[1]);
              _query += value[0] + " LIKE '" + value[1] + "' AND ";
            } else {
              throw new Error("$like usage {$like:[mobile: '1550114'], error with " + value);
            }
          } else if (key.toLowerCase() === '$gt') {
            value[1] = _filter(value[1]);
            _query += value[0] + ">'" + value[1] + "' AND ";
          } else if (key.toLowerCase() === '$gte') {
            value[1] = _filter(value[1]);
            _query += value[0] + ">= '" + value[1] + "' AND ";
          } else if (key.toLowerCase() === '$lt') {
            value[1] = _filter(value[1]);
            _query += value[0] + "<'" + value[1] + "' AND ";
          } else if (key.toLowerCase() === '$lte') {
            value[1] = _filter(value[1]);
            _query += value[0] + "<= '" + value[1] + "' AND ";
          } else if (key.toLowerCase() === '$neq') {
            value[1] = _filter(value[1]);
            _query += value[0] + " != '" + value[1] + "' AND ";
          } else if (key.toLowerCase() === '$nin') {  //不再里面包含
            if (!util.isArray(value)) {
              throw new Error('$nin value must be array, you supply is :' + value);
            } else if (!util.isArray(value[1])) {
              throw new Error('$nin must be like {$nin:["key", ["1", "2"]]}, you supply is :' + value[1]);
            }
            _query += value[0] + " NOT IN (";
            for(var j= 0, jj = value[1].length; j<jj; j++) {
              value[1][j] = _filter(value[1][j]);
              if (j===jj -1) {
                _query += "'" + value[1][j] + "'";
              } else {
                _query += "'" + value[1][j] + "',";
              }
            }
            _query = _query + ") AND ";
          //支持in操作
          } else if (util.isArray(value)) {
              _query += key + " IN (" ;
              for(var j= 0, jj = value.length; j<jj; j++) {
                if (j === jj-1) {
                  value[j] = _filter(value[j]);
                  _query += "'" + value[j] + "'";
                } else {
                  value[j] = _filter(value[j]);
                  _query += "'" + value[j]+"',";
                }
              }
              _query = _query + ") AND ";
          } else {
            //普通字符串
            value = _filter(value);
            _query += key + "='" + value + "' AND "  ;
          }
        }
        _query += ' 1=1 ) ';
    } else {
        _query = '';
    }
    if (!_query) {
        _query += ' 1=1 ';
    }
    return _query;
}

function _get_updates(updates) {
    var _update = ' SET ';
    if (!updates) {
        throw new Error("update is null");
    } else if (typeof updates === 'object') {
        var keys = Object.keys(updates);
        var values = [];
        for(var i= 0, ii=keys.length; i<ii; i++) {
          var key = keys[i];
          var value = updates[keys[i]];
          // 如果是object转为json
          if (Object.prototype.toString.call(value) === '[object Object]') {
            _update += '"' + key + '"= $'+(i+1) + '::json,';
          } else if (Object.prototype.toString.call(value) === '[object Array]') {
            _update += '"' + key + '"= $' +(i+1) + ',';
          } else {
            _update += '"' + key + '"= $' + (i+1) + ',';
          }
          values.push(value);
//          value = _filter(value);
//          _update += key + "='" + value + "',";
        }
        //去除最后逗号
        _update = _update.substr(0, _update.length-1);
        return [_update, values];
    } else if (typeof updates === 'string') {
        _update = updates;
        return _update;
    } else {
        throw new Error('not support updates typeof :'+ typeof updates);
    }
}

function _filter(str) {
    if (str && typeof str === 'string') {
      str = str.replace(/(;)/ig, '');
    }
    return str;
}

function _wrapperSql(sql, opts) {
    if (!opts) {
        opts = {};
    }
    var columns = opts.columns;
    var query = opts.query;
    var update = opts.update;
    var table = opts.table;

//    console.info("原始SQL\n"+sql);
    sql = sql.replace(/:columns/, columns);
    sql = sql.replace(/:query/, query);
    sql = sql.replace(/:table/, table);
    sql = sql.replace(/:update/, update);
//    console.info("wrapSQL\n"+sql);
    return sql;
}

/**
 * 简化从BaseProxy继承
 * @param model
 * @returns {ProxyClass}
 */
BaseProxy.factory = function(model) {
  function ProxyClass() {
    BaseProxy.call(this, model);
  }
  util.inherits(ProxyClass, BaseProxy);
  return ProxyClass;
}

/**
 * 获取一个实例化对象
 * @param model
 * @returns {BaseProxy}
 */
BaseProxy.instance = function(model) {
  var proxy = new BaseProxy(model);
  return proxy;
}

module.exports = BaseProxy;